Transaction Based Processing In MS Access Using DAO

MS Access is able for doing transaction-based processing and passes them to any of the backend DBMS system. As the result, transaction is managed by the DBMS rather than by MS Access. SQL commands are passed to DBMS and executed by using DAO Connection object. Here is an example shown to explain the complete criteria.We have created a table with the fields and structure of the table is shown in figure Fig 1.1

Create a form with a button for which you will implement the Code.Details has been shown in figure Fig 1.2

When you will click on to the button then connection will get established and transaction will start and after successful transaction a message box will appear.Details has been shown in figure Fig 1.3

After the successful transaction, a new record will get stored into the table. Details has been shown in figure Fig 1.4

If the data already exist into the table and you will click on to the transaction button then a message box will open telling about the failure of transaction.Details has been shown below in figure Fig 1.5

Here is the complete VBA code given for the above procedure :-

Public Function transaction() As Boolean

On Error GoTo Err_Handler

Dim wrkCurrent As DAO.Workspace
Dim blnInTrans As Boolean
Dim query1 As String
Dim intRowsEffected As Integer
Set wrkCurrent = DAO.DBEngine.Workspaces(0)
query1 = "insert into table1(custid, custname,custbal) values (1,'abhinay',100)"
wrkCurrent.BeginTrans
CurrentDb.Execute query1, dbFailOnError
wrkCurrent.CommitTrans
transaction = True
Exit Function

Err_Handler:
wrkCurrent.Rollback
transaction = False
End Function

'---------------Code for On Click event of the "Transaction" button---------------

Private Sub cmdTrans_Click()
If transaction Then
MsgBox "Transaction successfully made", vbInformation, "Success"
Else
MsgBox "Transaction Failed", vbInformation, "Failure"
End If
End Sub